from IPython.display import HTML

HTML('''<script>
code_show=true; 
function code_toggle() {
 if (code_show){
 $('div.input').hide();
 } else {
 $('div.input').show();
 }
 code_show = !code_show
} 
$( document ).ready(code_toggle);
</script>
<form action="javascript:code_toggle()"><input type="submit" value="Click here to toggle on/off the raw code."></form>''')

import pandas as pd
expense = pd.read_csv(r"C:\Users\yilin\OneDrive\Desktop\red cross\charitydata-119219814RR0001-expenses.csv", skiprows=1, index_col=0)
total_expense = expense.iloc[-1,::-1].to_frame()
total_expense

	Total expenditures ($)
2003	191334231.0
2004	195510000.0
2005	0.0
2006	283949891.0
2007	257229755.0
2008	299197026.0
2009	415271172.0
2010	366804504.0
2011	419674677.0
2012	386445763.0
2013	306388801.0
2014	282271763.0
2015	310099360.0
2016	313153540.0
2017	516908994
2018	457956426
2019	381758320
2020	347602783
2021	553402595

# change data type
total_expense = total_expense['Total expenditures ($)'].astype('int64')

revenue = pd.read_csv(r"C:\Users\yilin\OneDrive\Desktop\red cross\charitydata-119219814RR0001-revenue.csv", skiprows=1, index_col=0)
total_revenue = revenue.iloc[-1,::-1].to_frame()
total_revenue

	Total revenue ($)
2003	191388361.0
2004	197908000.0
2005	485211918.0
2006	362879651.0
2007	230604119.0
2008	253196921.0
2009	325330082.0
2010	449211535.0
2011	403926824.0
2012	349698551.0
2013	255564842.0
2014	328775664.0
2015	275720312.0
2016	314648445.0
2017	612082298
2018	476646615
2019	340074588
2020	336899823
2021	563961036

# change data type
total_revenue = total_revenue['Total revenue ($)'].astype('int64')

# data visualization
# define colors
GRAY1, GRAY2, GRAY3 = '#231F20', '#414040', '#555655'
GRAY4, GRAY5, GRAY6 = '#646369', '#76787B', '#828282'
GRAY7, GRAY8, GRAY9 = '#929497', '#A6A6A5', '#BFBEBE'
BLUE1, BLUE2, BLUE3, BLUE4 = '#174A7E', '#4A81BF', '#94B2D7', '#94AFC5'
RED1, RED2 = '#C3514E', '#E6BAB7'
GREEN1, GREEN2 = '#0C8040', '#9ABB59'
ORANGE1 = '#F79747'

import plotly.graph_objects as go
import plotly.express as px

# line plot
fig = go.Figure(data=go.Scatter(x=total_expense.index.values[-5:],
                                y=total_expense.values[-5:],
                                mode='lines',
                                line=dict(color=ORANGE1, width=3),
                                name='Total Expense'))

fig.add_trace(
    go.Scatter(x=total_revenue.index.values[-5:],
               y=total_revenue.values[-5:],
               mode='lines',
               line=dict(color=BLUE1, width=3),
               name='Total Revenue'))

# scatter plot
fig.add_trace(
    go.Scatter(x=total_revenue.index.values[-3:],
               y=total_revenue.values[-3:],
               mode='markers+text',
               marker=dict(color=BLUE1, size=12),
               name='Total Revenue'))
fig.add_trace(
    go.Scatter(x=total_expense.index.values[-3:],
               y=total_expense.values[-3:],
               mode='markers+text',
               marker=dict(color=ORANGE1, size=12),
               name='Total Expense'))

# vertical line
fig.add_vline(x=2, line_width=2.5, line_color=GRAY1)

# yaxes
fig.update_yaxes(range=(0, 700000000),
                 title_text='$',
                 title_standoff=15,
                 title_font=dict(size=16, family='Arial'),
                 tickfont=dict(size=16, family='Arial'),
                 automargin=True)



# annotation
annotation = []

# label lines directly
annotation.append(
    dict(xref='paper',
         yref='paper',
         x=0,
         y=0.95,
         text='Total Revenue',
         font=dict(family='Arial', size=15, color=BLUE1),
         showarrow=False))

annotation.append(
    dict(
        xref='paper',
        yref='paper',
        x=-0.1,
        y=-0.27,
        xanchor='left',
        yanchor='bottom',
        text='Data source: red cross website',
        font=dict(family='Arial', size=11.5, color=GRAY3),
        showarrow=False,
        align='left'))

annotation.append(
    dict(xref='paper',
         yref='paper',
         x=0,
         y=0.65,
         text='Total Expense',
         font=dict(family='Arial', size=15, color=ORANGE1),
         showarrow=False))

annotation.append(
    dict(xref='paper',
         yref='paper',
         x=1.12,
         y=1.12,
         text='<b>From 2019 to 2020, our expense are higher than revenue</b><br>We nearly maintain a balance between revenue and expense in 2021',
         font=dict(family='Arial', size=13, color=GRAY5),
         align='left',
         showarrow=False))

annotation.append(
    dict(x=-0.1,
         y=1.45,
         xref='paper',
         yref='paper',
         text='<b>Please cut down on our expenses</b>',
         font=dict(size=30, color=GRAY3),
         showarrow=False))

annotation.append(
    dict(x=-0.1,
         y=1.32,
         xref='paper',
         yref='paper',
         text='to balance the budget',
         font=dict(size=20, color=GRAY7),
         showarrow=False))

annotation.append(
    dict(x=-0.1,
         y=1.2,
         xref='paper',
         yref='paper',
         text='total revenue vs total expense over time',
         font=dict(family='Arial', size=18, color=GRAY2),
         showarrow=False))

fig.update_layout(template='simple_white',
                  font=dict(family='Arial', size=15, color='#646369'),
                  showlegend=False,
                  margin=dict(t=150, r=100),
                  height= 500,
                  width=800,
                  annotations = annotation)

fig.show()
fig.write_html("total Revenue VS Total Expense.html")

From above line chart, we can clearly see the trend of total revenue and total expense over time in the recent 5 years.

• Total Revenue reached the highest in 2017 but dropped the lowest in 2019.
• From 2017 to 2018, our revenues can cover our expenses.
• But situation changed in 2019. Our revenue started to unable to cover our expense.
• Since 2020, we nearly keep the balance the revenue and expense.
• Our revenue increased dramatically in 2021. So is expense.